package cn.edu.sdjzu.xg.bysj.dao;
//201902104061徐中堂
import cn.edu.sdjzu.xg.bysj.domain.ProfTitle;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j public final class ProfTitleDao {
    //创建 ProfTitleDao对象
    private static ProfTitleDao profTitleDao = new ProfTitleDao();
    //私有空构造器
    private ProfTitleDao() {
    }
    //获取实例
    public static ProfTitleDao getInstance() {
        return profTitleDao;
    }
    /**
     * 根据条件和分页查询职称
     * @param connection
     * @param condition
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<ProfTitle> findAll(Connection connection,Collection<Condition> condition, Pagination pagination)throws SQLException{
        Collection<ProfTitle> profTitles = new TreeSet<>();
        //构建查询语句
        StringBuilder select_sql = new StringBuilder("SELECT * FROM ProfTitle");
        if (condition != null){
            //为查询语句构建where子句
            select_sql.append(Condition.toWhereClause(JSON.toJSONString(condition)));
            log.trace(select_sql.toString());
        }
        if (pagination != null){
            //为查询语句构建分页limit子句
            select_sql.append(pagination.toLimitClause(CommonDao.getInstance().count(connection,"ProfTitle")));
            log.trace(select_sql.toString());
        }
        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement(select_sql.toString());
        //执行并获得查找到的结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //获得数据
        while (resultSet.next()){
            ProfTitle foundProfTitle = null;
            int profTitle_id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            foundProfTitle = new ProfTitle(profTitle_id,description,no,remarks);
            profTitles.add(foundProfTitle);
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的结果集
        return profTitles;
    }

    /**
     * 根据id查找
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public ProfTitle find(Connection connection,Integer id)throws SQLException{
        //预编译sql
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM ProfTitle WHERE id = ?");
        //查找对应id的元组
        preparedStatement.setInt(1,id);
        //执行并获得查找到的结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        ProfTitle foundProfTitle = null;
        //获得数据
        if (resultSet.next()){
            int profTitle_id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            foundProfTitle = new ProfTitle(profTitle_id,description,no,remarks);
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的对象
        return foundProfTitle;
    }

    /**
     * 更新
     * @param connection
     * @param profTitle
     * @return
     * @throws SQLException
     */
    public boolean update(Connection connection,ProfTitle profTitle)throws SQLException{
        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE ProfTitle SET description=?,no=?,remarks=? WHERE id=?");
        //设置修改之后的属性
        preparedStatement.setString(1,profTitle.getDescription());
        preparedStatement.setString(2,profTitle.getNo());
        preparedStatement.setString(3,profTitle.getRemarks());
        preparedStatement.setInt(4,profTitle.getId());
        //执行获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回更新判定
        return rowAffected>0;
    }

    /**
     *添加
     * @param connection
     * @param profTitle
     * @return
     * @throws SQLException
     */
    public boolean add(Connection connection,ProfTitle profTitle)throws SQLException {
        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO proftitle (description,no,remarks) values (?,?,?)");
        //设置添加的值
        preparedStatement.setString(1,profTitle.getDescription());
        preparedStatement.setString(2,profTitle.getNo());
        preparedStatement.setString(3,profTitle.getRemarks());
        //执行并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回添加判定
        return rowAffected>0;
    }

    /**
     * 删除
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean delete(Connection connection,Integer id)throws SQLException{
        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM proftitle WHERE id=?");
        //设置删除的id
        preparedStatement.setInt(1,id);
        //执行并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回删除判定
        return  rowAffected>0;
    }
}

